package com.scalar.chiptrack.operations.process.dao;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.HashMap;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.scalar.ScalarException;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.operations.process.BinSetup;
import com.scalar.chiptrack.operations.process.StepProperty;
import com.scalar.chiptrack.operations.process.ProcessTrack;
import com.scalar.chiptrack.operations.LotView;

public class ProcessTrackDAO{

	public static final int MARK_AND_PACK=0;
	public static final int FG = 1;

	private ProcessTrackDAO(){
	}

	public static ProcessTrack[] getProcessView(int productId) throws ScalarException{

		ArrayList list = getTrackIDs(productId);
		ProcessTrack[] processView = new ProcessTrack[list.size()];

		for(int i=0; i<list.size(); i++){
			processView[i] = new ProcessTrack();
			int trackID = Integer.parseInt( (String)list.get(i) );
            // changed by hareesh on 3rd October 2003
            // -- START --
			//String previousStepID = setFabAndSortInfo(trackID, processView[i]);
			//setAssemblyList(trackID, previousStepID, processView[i]);
            String values[] = setFabAndSortInfo(trackID,processView[i]);
            setAssemblyList( trackID, values[0], processView[i], values[1] );
            // -- END --
		}

		return processView;
	}

	public static ArrayList getTrackIDs(int productId) throws ScalarException{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		ArrayList trackIDList = new ArrayList();

		try{
			conn = DataSource.getConnection();
			ps = conn.prepareStatement(SqlQueryMappings.get("operations.process.track.getTrackIDs"));
			ps.setInt(1, productId);
			rs = ps.executeQuery();

			while(rs.next()){
				trackIDList.add(rs.getInt("trackID")+"");
			}

		}catch(SQLException sql){
			sql.printStackTrace();
			throw new ScalarException("tracking Fab failed ", sql);

		}finally{
			if(rs != null){
				try{  rs.close(); }catch(SQLException sql){}
			}
			if(ps != null){
				try{ ps.close(); }catch(Exception e){}
			}
			if(conn != null){
				try{ conn.close(); }catch(Exception e){}
			}
		}
		return trackIDList;
	}

    // changed by hareesh on 3rd October 2003
    // to retive 2 values
	//public static String setFabAndSortInfo(int trackID, ProcessTrack process) throws ScalarException{
      public static String[] setFabAndSortInfo(int trackID, ProcessTrack process) throws ScalarException{
      	Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
		ArrayList sort = new ArrayList();
        String previousStepID = null;

        // added by hareesh on 3rd October 2003
        String previousStepStatus = null;

        try{
           conn = DataSource.getConnection();
           ps = conn.prepareStatement(SqlQueryMappings.get("operations.process.track.selectFabTrackTable"));
		   ps.setInt(1, trackID);
           rs = ps.executeQuery();
           StepProperty step = null;

       	    while(rs.next()){
                step = new StepProperty();
                step.setProcessType("FAB");
                step.setProductName(rs.getString("Fab_Name"));
                step.setTrackID(trackID);
                step.setProcessStepID(rs.getInt("ProcessStep_ID")+"");
                step.setStatus(rs.getString("Status"));
                previousStepID = step.getProcessStepID();
                previousStepStatus = step.getStatus(); // added by hareesh on 3rd October 2003
                process.setFab(step);
     		}
            step = null;
            rs.close();
            rs = null;
            ps.close();
            ps = null;

            ps = conn.prepareStatement(SqlQueryMappings.get("operations.process.track.selectSortTrackTable"));
            ps.setInt(1, trackID);
            rs = ps.executeQuery();

			while(rs.next()){
				step = new StepProperty();
				step.setTrackID(trackID);
				step.setProcessType(rs.getString("Process_Step_Type"));
				step.setProcessStepID(rs.getInt("ProcessStep_ID")+"");
				step.setStatus(rs.getString("Status"));
				step.setPreviousStepID(previousStepID);
                step.setPreviousStepStatus(previousStepStatus); // added by hareesh on 3rd October 2003
				if((step.getProcessType()).startsWith("Wafer")){
					process.setWaferStore(step);
				}else if( (step.getProcessType()).startsWith("Sort") ){
					sort.add(step);
				}else{
					process.setDieBank(step);
				}
				previousStepID = step.getProcessStepID();
                previousStepStatus = step.getStatus(); // added by hareesh on 3rd October 2003
			}
			process.setSort(sort);
        }catch(SQLException sql){
            sql.printStackTrace();
            throw new ScalarException("Sort info reading failed", sql);
        }finally{
                if(rs != null){
                    try{ rs.close();  }catch(SQLException sql){}
                }
                if(ps != null){
                    try{ ps.close(); }catch(Exception e){}
                }
                if(conn != null){
                    try{ conn.close(); }catch(Exception e){}
                }
        }
		// changed by hareesh on 3rd October 2003
        // -- START --
        //return previousStepID;
        String returnValues[] =  { previousStepID, previousStepStatus };
        return returnValues;
        // -- END --
	}
    //changed by hareesh on 3rd October 2003
	//public static void setAssemblyList(int trackID, String previousStepID, ProcessTrack process) throws ScalarException{
    public static void setAssemblyList(int trackID, String previousStepID, ProcessTrack process, String previousStepStatus) throws ScalarException{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		StepProperty[] list = null;
        HashMap test = new HashMap();
		HashMap markAndFg = new HashMap();
		HashMap classStore = new HashMap();

		try{
			conn = DataSource.getConnection();
            ps = conn.prepareStatement(SqlQueryMappings.get("operations.process.track.selectCountAssemblyTrackTable"));
			ps.setInt(1, trackID);
            rs = ps.executeQuery();
			int length =0;

			if(rs.next() != false){
				length = rs.getInt("length");
			}

			list = new StepProperty[length];
			for(int i=0; i<length; i++){
				list[i] = new StepProperty();
			}

			rs.close();
			rs = null;

			ps.close();
			ps = null;

            ps = conn.prepareStatement(SqlQueryMappings.get("operations.process.track.selectAssemblyTrackTable"));
			ps.setInt(1, trackID);
			rs = ps.executeQuery();
			int i=0;

			while(rs.next()){
				StepProperty step = new StepProperty();
				step.setProcessType("ASSEMBLY");
				step.setProductName(rs.getString("Product_Name"));
				step.setTrackID(rs.getInt("AssemblyTrack_ID"));
				step.setProcessStepID(rs.getInt("ProcessStep_ID")+"");
				step.setStatus(rs.getString("Status"));
				step.setPreviousStepID(previousStepID);
                step.setPreviousStepStatus( previousStepStatus ); // added by hareesh on 3rd October 2003
				list[i] = step;
				//setTests(step, test, markAndFg, classStore); // changed by hareesh on 3rd October 2003
                setTests ( step, test, markAndFg, classStore, step.getStatus() ); // added by hareesh on 3rd October 2003
				i++;
			}
			process.setAssemblyList(list);
			process.setTest(test);
            process.setClassStore(classStore);
			process.setMarkAndFG(markAndFg );

        }catch(SQLException sql){
            sql.printStackTrace();
            throw new ScalarException("Reading Assembly tracks failed", sql);
        }finally{
			if(rs != null){
				try{ rs.close();  }catch(SQLException sql){}
			}
			if(ps != null){
				try{ ps.close(); }catch(Exception e){}
			}
			if(conn != null){
				try{ conn.close(); }catch(Exception e){}
			}
        }
	}
      // changed by hareesh on 3rd October 2003
      // public static void setTests(StepProperty asyStep, HashMap test, HashMap markAndFg, HashMap classStore) throws ScalarException{
      public static void setTests(StepProperty asyStep, HashMap test, HashMap markAndFg, HashMap classStore, String prevousStepStatus ) throws ScalarException{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList list = new ArrayList();
        boolean flag = false;
        String classStoreStepID = null;
        StepProperty binStep = null;
        String previousStepStatus1 = prevousStepStatus; // added by hareesh on 3rd October 2003
        // here i have taken another variable by extesion 1 because,
        //  for method calling for obejcts, between methods it act as Pass by Reference
        // and for preventive variables it acts as pass by value

        try
        {
            conn = DataSource.getConnection();
            ps = conn.prepareStatement(SqlQueryMappings.get("operations.process.track.selectTestTrackTable"));
			ps.setInt(1, asyStep.getTrackID());
            rs = ps.executeQuery();
			String previousStep = asyStep.getProcessStepID();

            while(rs.next())
            {
            	StepProperty step = new StepProperty();
				step.setTrackID(asyStep.getTrackID());
				step.setProcessType(rs.getString("Process_Step_Type"));
				step.setBinSetup((rs.getInt("Bin_Setup") == 0)? true:false);
				step.setProcessStepID(rs.getInt("ProcessStep_ID")+"");
				step.setStatus(rs.getString("Status"));
				step.setPreviousStepID(previousStep);
				step.setProductName(asyStep.getProductName());

                step.setPreviousStepStatus( previousStepStatus1 ); // added by hareesh on 3rd October 2003

				//if(step.getProcessType().equalsIgnoreCase("ClassStore"))
                if(step.getProcessType().equalsIgnoreCase("Class Store")) // changed by hareesh on 3rd October 2003
				{
					classStore.put(asyStep.getProductName(), step);
                	flag = true;
                	classStoreStepID = step.getProcessStepID();
				}
				else
				{
					list.add(step);
				}

				if(step.getBinSetup() == true)
				{
            		binStep = step;
	            //setMarkAndPack_FG(asyStep, step, markAndFg, flag, classStoreStepID);
				}
				previousStep = step.getProcessStepID();
                previousStepStatus1 = step.getStatus(); // added by hareesh on 3rd October 2003
          	}

            // changed by hareesh on 3rd October 2003
            //setMarkAndPack_FG(asyStep, binStep, markAndFg, flag, classStoreStepID );
            setMarkAndPack_FG(asyStep, binStep, markAndFg, flag, classStoreStepID , previousStepStatus1 );

            test.put(asyStep.getProductName(), list);

        }
        catch(SQLException sql)
        {
            sql.printStackTrace();
            throw new ScalarException("test track failed", sql);
        }
        finally
        {
			if(rs != null)
			{
				try{ rs.close();  }catch(SQLException sql){}
			}
			if(ps != null)
			{
				try{ ps.close(); }catch(Exception e){}
			}
			if(conn != null)
			{
				try{ conn.close(); }catch(Exception e){}
			}
		}
	}

    // changed by hareesh on 3rd October 2003
	//public static void setMarkAndPack_FG(StepProperty asyStep, StepProperty testStep, HashMap markAndFg, boolean flag, String classStoreStepID) throws ScalarException{
    public static void setMarkAndPack_FG(StepProperty asyStep, StepProperty testStep, HashMap markAndFg, boolean flag, String classStoreStepID, String previousStepStatus ) throws ScalarException{

		Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList[] list = null;
        String previousStepStatus1 = previousStepStatus; // added by hareesh on 3rd October 2003
        // here i have taken another variable by extesion 1 because,
        //  for method calling for obejcts, between methods it act as Pass by Reference
        // and for preventive variables it acts as pass by value

        try
        {
            conn = DataSource.getConnection();
			ps = conn.prepareStatement(SqlQueryMappings.get("operations.process.track.selectCountMarkFGTrackTable"));
			ps.setInt(1, asyStep.getTrackID());
			ps.setString(2, testStep.getProcessType());
			rs = ps.executeQuery();
			int length = 0;
			if(rs.next() != false)
			{
				length = rs.getInt("length");
			}

			list = new ArrayList[length];
			rs.close();
			rs = null;

			ps.close();
			ps = null;

            ps = conn.prepareStatement(SqlQueryMappings.get("operations.process.track.selectMarkFGTrackTable"));
			ps.setInt(1, asyStep.getTrackID());
			ps.setString(2, testStep.getProcessType());
            rs = ps.executeQuery();
            StepProperty step = null;

            int i=0;
            String previousStepID = null;
            if(flag)
            {
            	previousStepID = classStoreStepID;
            }
            else
            {
			 	previousStepID = testStep.getProcessStepID();
            }
			String markStepID  = null;
            while(rs.next())
            {
                String previousStepStatus2 = previousStepStatus1; // added by hareesh on 3rd October 2003

			  	step = new StepProperty();
				step.setTrackID(asyStep.getTrackID());
				step.setProcessType(rs.getString("Mark_Pack"));
				step.setProductName(rs.getString("Product_Name"));
				step.setProcessStepID(rs.getInt("Mark_Pack_ProcessStep_ID")+"");
				step.setStatus(rs.getString("Mark_Pack_Status"));
				step.setPreviousStepID(previousStepID);
				step.setPreviousStepName(testStep.getProcessType());

                step.setPreviousStepStatus( previousStepStatus2 ); // added by hareesh on 3rd October 2003
                previousStepStatus2 = step.getStatus(); // added by hareesh on 3rd October 2003

                list[i] = new ArrayList();
                list[i].add(step);
				markStepID = step.getProcessStepID();
                step = null;

                step = new StepProperty();
				step.setTrackID(asyStep.getTrackID());
				step.setProcessType(rs.getString("FG"));
				step.setProductName(rs.getString("Product_Name"));
				step.setProcessStepID(rs.getInt("FG_ProcessStep_ID")+"");
				step.setStatus(rs.getString("FG_Status"));
				step.setPreviousStepID(markStepID);
				step.setPreviousStepName(testStep.getProcessType());
                step.setPreviousStepStatus( previousStepStatus2 ); // added by hareesh on 3rd October 2003

               	list[i].add(step);

               	i++;
            }
			markAndFg.put(asyStep.getProductName(), list);
		}
        catch(SQLException sql)
        {
        	sql.printStackTrace();
            throw new ScalarException("Reading Mark&Pack and FG failed", sql);

        }
        finally
        {
			if(rs != null)
			{
				try{ rs.close();  }catch(SQLException sql){}
			}
			if(ps != null)
			{
				try{ ps.close(); }catch(Exception e){}
			}
			if(conn != null)
			{
				try{ conn.close(); }catch(Exception e){}
			}
    	}
	}

	public static void saveGoodBinPartNumbers(DBTransaction dbt, int trackID, String stepName, Iterator it) throws ScalarException{
            Connection conn = null;
            PreparedStatement ps = null;
            boolean success = false;

            try {

            	// Get the connection from regular datasource if its not in a manual transaction
            	if (dbt == null) {
                    conn = DataSource.getConnection();
            	}else {
                    conn = dbt.getConnection();
            	}

                // STEP 1
                // Delete if any rows exist for this trackId

               // Commented this on Aug 27 2003 because the values have to be updated
               /* ps = conn.prepareStatement(SqlQueryMappings.get("operations.process.track.deleteGoodBinPartNumbersByTrackId"));
                ps.setInt (1, trackID);
                ps.executeUpdate();
                ps.close();
                ps = null; */


                // STEP 2
                // Save Bins
            	ps = conn.prepareStatement(SqlQueryMappings.get("operations.process.track.saveGoodBinPartNumbers"));

                String binProductName = null;

                while(it.hasNext()){
                        binProductName = (String)it.next();
                        ps.setInt(1, trackID);
                        ps.setString(2, stepName);
                        ps.setString(3, binProductName);
                        ps.setString(4, "Mark & Pack");
                        ps.setString(5, "Incomplete");
                        ps.setString(6, "FG");
                        ps.setString(7, "Incomplete");

                        ps.addBatch();
                }
                int temp[] = ps.executeBatch();
				//System.out.println("Assembly Track Id "+ trackID);
            	//System.out.println("NO of Operations entered in MARK_FG table "+temp.length);
            	success = true;

            }
            catch (Exception sqle) {
            	sqle.printStackTrace();
            	throw new ScalarException ("saveGoodBinPartNumbers() method failed in ProcessTrackDAO", sqle);
            }
            finally {

            	// Commit/Rollback here it selef -- if its not in a manual transaction
            	if (dbt == null) {
                    try {
                        if (conn != null) {
                            if (success == true) {
                                conn.commit();
                            }
                            else {
                                conn.rollback();
                            }
                        }
                    }
                    catch (SQLException ignore) {}
            	}

            	if (ps != null) {
                    try { ps.close(); } catch (SQLException ignore){}
            	}
            	if (conn != null) {
                    try { conn.close(); } catch (SQLException ignore){}
            	}
            }

	}

	public static void saveGoodBinPartNumbers(DBTransaction dbt, int trackID, String stepName, ArrayList goodbins) throws ScalarException{
		Connection conn = null;
		CallableStatement cstmt = null;
		PreparedStatement ps = null;
		boolean success = false;

		try
		{
        	// Get the connection from regular datasource if its not in a manual transaction
			if (dbt == null)
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbt.getConnection();
			}
			ps = conn.prepareStatement(SqlQueryMappings.get("operations.process.track.deleteGoodBinPartNumbersByTrackIdAndMarkPackId"));
			ps.setInt (1, trackID);
			ps.executeUpdate();
			ps.close();
			ps = null;

			cstmt = conn.prepareCall("{Call sp_update_markfg_product_inventory(?,?,?,?,?)}");

			for (int i=0 ; i < goodbins.size() ; i++ )
			{
				BinSetup bin = (BinSetup)goodbins.get(i);
				cstmt.setString(1, bin.getPartNumber());
				cstmt.setString(2, stepName);
				cstmt.setInt(3, trackID);
				cstmt.setInt(4, Integer.parseInt(bin.getMark_Pack_ProcessStep_ID()));
				cstmt.setInt(5, Integer.parseInt(bin.getFG_ProcessStep_ID()));
				cstmt.addBatch();
			}
			cstmt.executeBatch();



		/*	ps = conn.prepareStatement(SqlQueryMappings.get("operations.process.track.deleteGoodBinPartNumbersByTrackIdAndMarkPackId"));
			ps.setInt (1, trackID);
			ps.executeUpdate();
			ps.close();
			ps = null;

			// STEP 1
			// Save Bins
			ps = conn.prepareStatement(SqlQueryMappings.get("operations.process.track.saveGoodBinPartNumbers"));

			String binProductName = null;

			// This is for new items added to the bin

			for (int i=0 ; i < goodbins.size() ; i++ )
			{
				BinSetup bin = (BinSetup)goodbins.get(i);

				if(  bin.getMark_Pack_ProcessStep_ID() == null || bin.getMark_Pack_ProcessStep_ID().equals("-1") || bin.getMark_Pack_ProcessStep_ID().equals("-2") )
				{
					ps.setInt(1, trackID);
					ps.setString(2, stepName);
					ps.setString(3, bin.getPartNumber());
					ps.setString(4, "Mark & Pack");
					ps.setString(5, "Incomplete");
					ps.setString(6, "FG");
					ps.setString(7, "Incomplete");
					ps.addBatch();
				}
			}
			int temp[] = ps.executeBatch();

			ps.close();
			ps = null;
			ps = conn.prepareStatement(SqlQueryMappings.get("operations.process.track.updateGoodBinPartNumbers"));

			// This is for Updation of items already configured

			for (int i=0 ; i < goodbins.size() ; i++ )
			{
				BinSetup bin = (BinSetup)goodbins.get(i);
				if( bin.getMark_Pack_ProcessStep_ID()!=null && !bin.getMark_Pack_ProcessStep_ID().equals("-1")  && !bin.getMark_Pack_ProcessStep_ID().equals("-2"))
				{
					ps.setString(1, bin.getPartNumber());
					ps.setString(2, bin.getMark_Pack_ProcessStep_ID());
					ps.addBatch();
				}
			}
			temp = ps.executeBatch();
			//System.out.println("NO of Operations updated in MARK_FG table "+temp.length); */
			success = true;

		}
		catch (Exception sqle) {
			sqle.printStackTrace();
			throw new ScalarException ("saveGoodBinPartNumbers() method failed in ProcessTrackDAO", sqle);
		}
		finally {

			// Commit/Rollback here it selef -- if its not in a manual transaction
			if (dbt == null) {
				try {
					if (conn != null) {
						if (success == true) {
							conn.commit();
						}
						else {
							conn.rollback();
						}
					}
				}
				catch (SQLException ignore) {}
			}

			if (ps != null) {
				try { ps.close(); } catch (SQLException ignore){}
			}
			if(cstmt != null){
				try { cstmt.close(); } catch (SQLException ignore){}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore){}
			}
		}

	}


    // added by hareesh on 1st Ocotber 2003
    public static void updateMarkAndFG(DBTransaction dbt, int processStepID, int trackID, String prevStepName, String productName, int flag) throws ScalarException{
        updateMarkAndFG(dbt, processStepID,trackID, prevStepName, productName, flag, "Complete");
    }

    // changed by hareesh on 1st October 2003
	public static void updateMarkAndFG(DBTransaction dbt, int processStepID, int trackID, String prevStepName, String productName, int flag, String status) throws ScalarException{

		Connection conn = null;
        PreparedStatement ps = null;
        boolean success = false;

        try {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbt == null) {
                conn = DataSource.getConnection();
            }else {
                conn = dbt.getConnection();
            }

            if(flag == MARK_AND_PACK){
                ps = conn.prepareStatement (SqlQueryMappings.get ("operations.process.track.updateMarkAndFinishedGoods"));
            }else{
                ps = conn.prepareStatement(SqlQueryMappings.get ("operations.process.track.updateFinishedGoods"));
            }

            ps.setInt(1, processStepID);
            ps.setString(2, status);
            ps.setInt(3, trackID);
            ps.setString(4, prevStepName);
            ps.setString(5, productName);
            ps.executeUpdate();
            success = true;

       	}catch (Exception sqle) {
           	sqle.printStackTrace();
           	throw new ScalarException ("Updation on Mark_FG_Track_Table failed", sqle);
       	}finally {
           	// Commit/Rollback here it selef -- if its not in a manual transaction
           	if (dbt == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                                conn.rollback();
                            }
                        }
                    }catch (SQLException ignore) {}
            }
           	if (ps != null) {
                try { ps.close(); } catch (SQLException ignore){}
           	}
           	if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
           	}
    	}
    }

	// added by hareesh on 1st October 2003
    public static void updateFabTrack(DBTransaction dbt, int processStepID, int trackID ) throws ScalarException{
     updateFabTrack( dbt, processStepID,  trackID,  "Complete");
    }
    // changed by hareesh on 1st October 203
    public static void updateFabTrack(DBTransaction dbt, int processStepID, int trackID, String status ) throws ScalarException{
		Connection conn = null;
        PreparedStatement ps = null;
        boolean success = false;
       	try {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbt == null) {
               conn = DataSource.getConnection();
            }else {
               conn = dbt.getConnection();
            }
            ps = conn.prepareStatement (SqlQueryMappings.get ("operations.process.track.updateFabTrack"));
			ps.setInt(1, processStepID);
			ps.setString(2,status );
			ps.setInt(3, trackID);
			ps.executeUpdate();
           	success = true;
       	}catch (Exception sqle) {
           	sqle.printStackTrace();
           	throw new ScalarException ("Updation on Fab_Track_Table failed", sqle);
       	}finally {
           	// Commit/Rollback here it selef -- if its not in a manual transaction
           	if (dbt == null) {
          		try {
               		if (conn != null) {
                     	if (success == true) {
                       		conn.commit();
                       	}
                       	else {
                         	conn.rollback();
                        }
                   	}
              	}catch (SQLException ignore) {}
            }
           	if (ps != null) {
           		try { ps.close(); } catch (SQLException ignore){}
           	}
           	if (conn != null) {
           		try { conn.close(); } catch (SQLException ignore){}
           	}
       	}
	}
    // added by hareesh on 1st October 2003
    public static void updateSortAndWaferStoreTrack(DBTransaction dbt, int processStepID, int trackID , String stepName ) throws ScalarException{
        updateSortAndWaferStoreTrack(dbt, processStepID, trackID , stepName, "Complete" );
    }

    // changed by hareesh on 1st October 2003
	public static void updateSortAndWaferStoreTrack(DBTransaction dbt, int processStepID, int trackID , String stepName, String status ) throws ScalarException{
		Connection conn = null;
        PreparedStatement ps = null;
        boolean success = false;
       	try {
           	// Get the connection from regular datasource if its not in a manual transaction
           	if (dbt == null) {
         		conn = DataSource.getConnection();
           	}else {
          		conn = dbt.getConnection();
           	}
           	ps = conn.prepareStatement (SqlQueryMappings.get ("operations.process.track.updateSortAndWaferStoreTrack"));
			ps.setInt(1, processStepID);
			ps.setString(2, status );
			ps.setInt(3, trackID);
			ps.setString(4, stepName);
			int x = ps.executeUpdate();
           	success = true;
       	}catch (Exception sqle) {
           	sqle.printStackTrace();
           	throw new ScalarException ("Updation on Sort_Track_Table failed", sqle);
       	}finally {
           	// Commit/Rollback here it selef -- if its not in a manual transaction
           	if (dbt == null) {
             	try {
                    if (conn != null) {
                        if (success == true) {
                        	conn.commit();
                        } else {
                          	conn.rollback();
                        }
                   	}
                }catch (SQLException ignore) {}
            }
            if (ps != null) {
            	try { ps.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
            	try { conn.close(); } catch (SQLException ignore){}
            }
       }
	}

    // added by hareesh on 1st October 2003
    public static void updateAssemblyTrack(DBTransaction dbt, int processStepID, int trackID) throws ScalarException{
        updateAssemblyTrack( dbt, processStepID, trackID, "Complete" );
    }

    // changed by hareesh on 1st October 2003
	public static void updateAssemblyTrack(DBTransaction dbt, int processStepID, int trackID, String  status) throws ScalarException{
		Connection conn = null;
        PreparedStatement ps = null;
        boolean success = false;
       	try {
           	// Get the connection from regular datasource if its not in a manual transaction
           	if (dbt == null) {
           		conn = DataSource.getConnection();
           	}else {
           		conn = dbt.getConnection();
           	}
           	ps = conn.prepareStatement (SqlQueryMappings.get ("operations.process.track.updateAssemblyTrack"));
			ps.setInt(1, processStepID);
			ps.setString(2, status);
			ps.setInt(3, trackID);
			ps.executeUpdate();
           	success = true;
       	}catch (Exception sqle) {
           	sqle.printStackTrace();
           	throw new ScalarException ("Updation on Sort_Track_Table failed", sqle);
       	}finally {
           	// Commit/Rollback here it selef -- if its not in a manual transaction
           	if (dbt == null) {
           		try {
             		if (conn != null) {
                   		if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }catch (SQLException ignore) {}
            }
            if (ps != null) {
        	    try { ps.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
      	    }
     	}
	}

    // added by hareesh on 1st September 2003
    // -- START --
    public static void updateMarkAndPackTrackProductName(DBTransaction dbt, int processStepID, int trackID,String productName,String oldProductName ) throws ScalarException{
        Connection conn = null;
        PreparedStatement ps = null;
        boolean success = false;
        try {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbt == null) {
                   conn = DataSource.getConnection();
            }else {
                   conn = dbt.getConnection();
            }
            ps = conn.prepareStatement (SqlQueryMappings.get ("operations.process.track.updateMarkAndPackTrackProductName"));

            ps.setString(1,productName);
            ps.setString(2,oldProductName);
            ps.setInt(3, trackID);
            ps.setInt(4, processStepID);
            ps.executeUpdate();
            success = true;
        }catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Updation of Product Name for Mark And Pack Track Table failed", sqle);
        }finally {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbt == null) {
                 try {
                    if (conn != null) {
                          if (success == true) {
                              conn.commit();
                           }else {
                              conn.rollback();
                            }
                       }
                 }catch (SQLException ignore) {}
            }
            if (ps != null) {
                try { ps.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
           }
        }
	}
    // -- END --

      // added by hareesh on 26th August 2003
    // -- START --
    public static void updateAssemblyTrackProductName(DBTransaction dbt, int processStepID, int trackID,String productName) throws ScalarException{
	    Connection conn = null;
        PreparedStatement ps = null;
        boolean success = false;
        try {
           	// Get the connection from regular datasource if its not in a manual transaction
           	if (dbt == null) {
           		conn = DataSource.getConnection();
           	}else {
         		conn = dbt.getConnection();
           	}
           	ps = conn.prepareStatement (SqlQueryMappings.get ("operations.process.track.updateAssemblyTrackProductName"));
            ps.setString(1,productName);
		    ps.setInt(2, trackID);
            ps.setInt(3, processStepID);
            ps.executeUpdate();
           	success = true;
       	}catch (Exception sqle) {
          	sqle.printStackTrace();
           	throw new ScalarException ("Updation of Product Name for Assembly Track Table failed", sqle);
       	}finally {
          	// Commit/Rollback here it selef -- if its not in a manual transaction
           	if (dbt == null) {
           		try {
            		if (conn != null) {
                   		if (success == true) {
                   			conn.commit();
                  		}
                 		else {
                   			conn.rollback();
                 		}
              		}
           		}catch (SQLException ignore) {}
           	}
           	if (ps != null) {
           		try { ps.close(); } catch (SQLException ignore){}
           	}
           	if (conn != null) {
           		try { conn.close(); } catch (SQLException ignore){}
           	}
       	}
	}
    // -- END --

    // added by hareesh on 1st September 2003
    // -- START --
    public static void updateFinishedGoodsTrackProductName(DBTransaction dbt, int processStepID, int trackID,String productName,String oldProductName ) throws ScalarException{
        Connection conn = null;
        PreparedStatement ps = null;
        boolean success = false;
        try {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbt == null) {
               conn = DataSource.getConnection();
            }else {
               conn = dbt.getConnection();
            }
            ps = conn.prepareStatement (SqlQueryMappings.get ("operations.process.track.updateFinishedGoodsTrackProductName"));
            ps.setString(1,productName);
            ps.setString(2,oldProductName);
            ps.setInt(3, trackID);
            ps.setInt(4, processStepID);
            ps.executeUpdate();
            success = true;
        }catch (Exception sqle) {
             sqle.printStackTrace();
            throw new ScalarException ("Updation of Product Name for Finished Goods Track Table failed", sqle);
         }finally {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbt == null) {
               try {
                 if (conn != null) {
                      if (success == true) {
                          conn.commit();
                      } else {
                           conn.rollback();
                      }
                  }
                }catch (SQLException ignore) {}
            }
            if (ps != null) {
               try { ps.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
	}

    public static boolean checkPartNumberExistsInTestBinAndFGBraodAndKit ( String partNumber ) throws ScalarException{

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        boolean result = false;
        try{

            conn = DataSource.getConnection();
            ps = conn.prepareStatement(SqlQueryMappings.get("operations.process.product.checkPartNumberExistsinTestBin"));
            ps.setString( 1, partNumber );
            rs = ps.executeQuery();
            if ( rs.next() ) {
                result = true;
            }

            if(result == false){
                rs.close();
                ps.close();

                if(conn == null) conn = DataSource.getConnection();
                ps = conn.prepareStatement(SqlQueryMappings.get("operations.process.product.checkPartNumberExistsinFGforBraodAndKit"));
                ps.setString( 1, partNumber );
                rs = ps.executeQuery();
                if ( rs.next() ) {
                    result = true;
                }

            }

        }catch(Exception ex){
            ex.printStackTrace();
        }finally {
                if( rs != null ){
                    try { rs.close(); } catch (SQLException ignore){}
                }
                if (ps != null) {
               		try { ps.close(); } catch (SQLException ignore){}
            	}
            	if (conn != null) {
               		try { conn.close(); } catch (SQLException ignore){}
            	}
        }
        return result;
    }
    // -- END --
    // added by hareesh on 1st October2003
    public static void updateTestTrack(DBTransaction dbt, int processStepID, int trackID, String stepName) throws ScalarException{
        updateTestTrack( dbt, processStepID, trackID, stepName, "Complete" );
    }

    // changed by hareesh on 1st October 2003
	public static void updateTestTrack(DBTransaction dbt, int processStepID, int trackID, String stepName, String status) throws ScalarException{
	    Connection conn = null;
        PreparedStatement ps = null;
        boolean success = false;
       	try {
           	// Get the connection from regular datasource if its not in a manual transaction
           	if (dbt == null) {
            	conn = DataSource.getConnection();
            }else {
                conn = dbt.getConnection();
            }
            ps = conn.prepareStatement(SqlQueryMappings.get ("operations.process.track.updateTestTrack"));
			ps.setInt(1, processStepID);
            ps.setString(2,status);
			ps.setInt(3, trackID);
			ps.setString(4, stepName);
			int rowsUpdated = ps.executeUpdate();
           	success = true;
      	}catch (Exception sqle) {
          	sqle.printStackTrace();
           	throw new ScalarException ("Updation on Sort_Track_Table failed", sqle);
       	}finally {
           	// Commit/Rollback here it selef -- if its not in a manual transaction
           	if (dbt == null) {
        		try {
               		if (conn != null) {
                   		if (success == true) {
                			conn.commit();
                  		} else {
                         	conn.rollback();
                        }
                   }
                }catch (SQLException ignore) {}
            }
           	if (ps != null) {
         		try { ps.close(); } catch (SQLException ignore){}
           	}
           	if (conn != null) {
           		try { conn.close(); } catch (SQLException ignore){}
           	}
       	}
	}

        public static ArrayList getWipProcessFlow(String productId) throws ScalarException {

                ArrayList flow = null;
                Connection conn = null;
        	PreparedStatement ps = null;

                ResultSet rs = null;
                boolean success = false;

                try {
		         conn = DataSource.getConnection();
		         ps = conn.prepareStatement(SqlQueryMappings.get ("operations.process.track.functionGetWipProcessFlow"));
                         ps.setString(1, productId);

		         rs = ps.executeQuery();

                         if(rs != null){
                                flow = new ArrayList();
                         }
                         while(rs.next()){

                                LotView lview = new LotView();
                                lview.setProcessStepID(rs.getInt("ProcessStep_ID")+"");
                                lview.setCurrentProcess(rs.getString("PS_Type"));
                                lview.setLocationID(rs.getInt("Location_ID")+"");
                                lview.setVendorLocationName(rs.getString("Location"));
                                lview.setLotID(rs.getInt("Lot_ID")+"");
                                lview.setLotNumber(rs.getString("Lot_Number"));
                                flow.add(lview);

                         }
		         success = true;

		}catch (Exception sqle) {

		        sqle.printStackTrace();
		        throw new ScalarException ("getWipProcessFlow() method failed in ProcessTrackDAO", sqle);

		}finally {

			if (ps != null) {
			   try { ps.close(); } catch (SQLException ignore){}
			}

                        if (ps != null) {
			   try { ps.close(); } catch (SQLException ignore){}
			}

			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore){}
			}
		}
		return flow;
        }

        public static StringBuffer constructFlow(ArrayList list) throws ScalarException{

              StringBuffer sb = new StringBuffer();
              Iterator it = list.iterator();

              String previousStepID = "";
              String currentStepID = "";
              String currentLocationID = "";
              String previousLocationID = "";
              String locationVar = null;
              String productVar = null;
              String processStepVar = null;

              boolean flag = false;
              int i = 1;

              while(it.hasNext()){
                    //sb.append("i= "+(i++));
                    LotView lview = (LotView)it.next();

                    currentStepID = lview.getProcessStepID();
                    currentLocationID = lview.getLocationID();

                    if(currentStepID.equals(previousStepID)){
                        if(currentLocationID.equals(previousLocationID)){

                             if(flag){
                                  //sb.append(" Lot_ID = "+lview.getLotID()+"\n");
                                  if(lview.getLotNumber() != null){
	                                  sb.append(createLot( locationVar,
	                                                        lview.getLotNumber(),
	                                                        lview.getLotID(),
	                                                        "#"));
	                             	  sb.append("\n");
                                  }
                             }else{
                                  //sb.append("\t\t\t\t\t\t Lot_ID = "+lview.getLotID()+"\n");
                                  if(lview.getLotNumber() != null){
	                                  sb.append(createLot( locationVar,
	                                                        lview.getLotNumber(),
	                                                        lview.getLotID(),
	                                                        "#"));
	                             	  sb.append("\n");
                                  }
                             }

                        }else{

                             //sb.append("\t\t\t Location_ID = "+ currentLocationID+"\t");
                             locationVar = "location"+i;
                             sb.append(createLocation( productVar,
			     				locationVar,
                                                        lview.getVendorLocationName(),
                                                        currentLocationID,
                                                        "#"));
                             sb.append("\n");
                             if(flag){
                                  //sb.append(" Lot_ID = "+lview.getLotID()+"\n");
                                  if(lview.getLotNumber() != null){
	                                  sb.append(createLot( locationVar,
	                                                        lview.getLotNumber(),
	                                                        lview.getLotID(),
	                                                        "#"));
	                             	  sb.append("\n");
                                  }
                             }else{
                                  //sb.append(" Lot_ID = "+lview.getLotID()+"\n");
                                  if(lview.getLotNumber() != null){
	                                  sb.append(createLot( locationVar,
	                                                        lview.getLotNumber(),
	                                                        lview.getLotID(),
	                                                        "#"));
	                             	  sb.append("\n");
                                  }
                             }
                        }

                        flag = false;
                    }else{
                        flag = true;
                        //sb.append("ProcessStep_ID = "+currentStepID+"\t");
                        productVar = "product"+i;
                        sb.append(createProduct(productVar,
						(lview.getCurrentProcess()+i),
						currentStepID,
						"#"));
                        sb.append("\n");
                        sb.append(createProcessStep(productVar,
                                                    lview.getCurrentProcess(),
                                                    currentStepID,
                                                    "#"));
                        sb.append("\n");
                        if(currentLocationID.equals(previousLocationID)){
                             //sb.append(" Location_ID = "+ currentLocationID+"\t");
                             locationVar = "location"+i;
                             sb.append(createLocation( productVar,
			     				locationVar,
                                                        lview.getVendorLocationName(),
                                                        currentLocationID,
                                                        "#"));
                             sb.append("\n");

                             if(flag){
                                  //sb.append(" Lot_ID = "+lview.getLotID()+"\n");
                                  if(lview.getLotNumber() != null){
	                                  sb.append(createLot( locationVar,
	                                                        lview.getLotNumber(),
	                                                        lview.getLotID(),
	                                                        "#"));
	                             	  sb.append("\n");
                                  }
                             }else{
                                  //sb.append("\t\t\t\t\t\t Lot_ID = "+lview.getLotID()+"\n");
                                  if(lview.getLotNumber() != null){
	                                  sb.append(createLot( locationVar,
	                                                        lview.getLotNumber(),
	                                                        lview.getLotID(),
	                                                        "#"));
	                             	  sb.append("\n");
                                  }
                             }

                        }else{
                             //sb.append(" Location_ID = "+ currentLocationID+"\t");
                             locationVar = "location"+i;
                             sb.append(createLocation( productVar,
			     				locationVar,
                                                        lview.getVendorLocationName(),
                                                        currentLocationID,
                                                        "#"));
                             sb.append("\n");
                             if(flag){
                                  //sb.append(" Lot_ID = "+lview.getLotID()+"\n");
                                  if(lview.getLotNumber() != null){
	                                  sb.append(createLot( locationVar,
	                                                        lview.getLotNumber(),
	                                                        lview.getLotID(),
	                                                        "#"));
	                             	  sb.append("\n");
                                  }
                             }else{
                                  //sb.append("\t\t\t\t\t\t Lot_ID = "+lview.getLotID()+"\n");
                                  if(lview.getLotNumber() != null){
	                                  sb.append(createLot( locationVar,
	                                                        lview.getLotNumber(),
	                                                        lview.getLotID(),
	                                                        "#"));
	                             	  sb.append("\n");
                                  }
                             }

                        }

                        flag = false;

                    }

                    previousStepID = currentStepID;
                    previousLocationID = currentLocationID;
                    i++;
              }
              return sb;
        }

        public static String createLocation(String product, String jsVar, String name, String id, String file) {

                String temp = "";
                        temp += "var "+jsVar;
                        temp += " = new Location("+"'"+name.replaceAll("'","\\\\"+"'")+"', ";
                        temp += id+", "+"'"+file+"');";

                //jsVar = "location"+count;
                        temp += "\n";
                        temp += product+".addLocation("+jsVar+");";
                return  temp;
        }

        public static String createLot(String locName, String lotNumber, String id, String file){
                String temp = "";
                        temp += locName+".addLot(";
                        //Modified by Veeru on 17th August 2004
                        //temp += " new Lot("+"'"+lotNumber+"', ";
                        temp += " new Lot("+"'"+lotNumber.replaceAll("'","\\\\"+"'")+"', ";
                        temp += id+", "+"'"+file+"'));";
                return temp;
        }

        public static String createProduct(String proName, String name, String id, String file){
                String temp ="";
                        temp += "var "+proName;
                        temp += " = new Product("+"'"+name+"', ";
                        temp += id+", "+"'"+file+"');";
                return temp;
        }

        public static String createProcessStep(String proName, String name, String id, String file){
                String temp = "";
                        temp += "d.addProcessStep( new ProcessStep(";
                        temp += "'"+name+"', "+id+", ";
                        temp += "'"+file+"', "+proName+"));";

                return temp;

        }


	public static void main(String[] args) throws Exception{

		/*ProcessTrack[] list = null;

		try{
			list = getProcessView(1);
		}catch(Exception e){
			e.printStackTrace(System.err);
		}

		int length = list.length;
		System.out.println("List length = "+ length);
		StepProperty[] assemblyList = null;

		for(int i=0; i<length; i++){
			StepProperty fab = list[i].getFab();
			System.out.println(fab.getProcessType()+"; "+fab.getProductName()+"; "+fab.getStatus());
			assemblyList = list[i].getAssemblyList();

			for(int j=0; j<assemblyList.length; j++){
				System.out.println("\t i = "+i+"; "+assemblyList[j].getProcessType()+"; "+assemblyList[j].getProductName()+"; "+assemblyList[j].getStatus());

				System.out.println();
				ArrayList test_list = (ArrayList)(list[i].getTest().get(assemblyList[j].getProductName()));
				Iterator it = test_list.iterator();
				while(it.hasNext()){
					StepProperty step = (StepProperty)it.next();
					System.out.println("\t\t i = "+i+"; j="+j+"; "+step.getProcessType().trim()+"; "+step.getProductName()+"; "+step.getStatus());

				}
				System.out.println();
				ArrayList[] fgList = (ArrayList[])(list[i].getMarkAndFG().get(assemblyList[j].getProductName()));
				if(fgList != null){
					for(int k=0; k<fgList.length; k++){
						Iterator m = fgList[k].iterator();
						while(m.hasNext()){
							StepProperty fgStep = (StepProperty)m.next();
							System.out.println("\t\t\t j="+j+"; k="+k+"; "+fgStep.getProcessType()+"; "+ fgStep.getTrackID());
						}
					}
				}
			}

			System.out.println();
			System.out.println();
		}*/

                System.out.println(constructFlow(getWipProcessFlow(1052+"")).toString());
	}
}

